package com.wiiss.ams.dao;

import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.log.StaticLog;
import com.wiiss.ams.util.Const;
import com.wiiss.ams.util.Exc;

import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
import java.util.stream.Stream;

public class PostgresqlDML {

    private static final int BATCH_INSERT_SIZE = 1000;

    public static List<JSONObject> get(String database, String sql, LocalDateTime from, LocalDateTime to) {
        try {
            return DbFactory.get(database).query(sql, Map.of("from", from, "to", to)).stream().map(JSONObject::new).toList();
        } catch (SQLException e) {
            throw new Exc(e, "postgresql query failed");
        }
    }

    public static int deleteBefore(String database, String table, String byField, LocalDateTime beforeTime) {
        int res = 0;
        try {
            res = DbFactory.get(database).execute(StrUtil.format("""
                                                                 DELETE FROM {} WHERE {} < '{}'
                                                                 """, table, byField, Const.DATE_TIME_FORMATTER.format(beforeTime)));
        } catch (SQLException e) {
            throw new Exc(e, "delete failed");
        }
        return res;
    }

    public static int truncate(String database, String table) {
        AtomicInteger effectedRow = new AtomicInteger(0);
        try {
            DbFactory.get(database).tx(db -> {
                effectedRow.addAndGet(db.execute(StrUtil.format("""
                                                                TRUNCATE {}
                                                                """, table)));
            });
        } catch (SQLException e) {
            throw new Exc(e, "postgresql truncates failed");
        }
        return effectedRow.get();
    }

    public static Integer batchInsert(String database, String table, List<JSONObject> jsonObjects) {
        if (jsonObjects.isEmpty()) {
            return 0;
        }

        // 组装 insert sql
        List<String> fields = jsonObjects.get(0).keySet().stream().toList();

        List<String> insertSqls = segment(jsonObjects, BATCH_INSERT_SIZE).stream()
                .map(list -> list.stream()
                        .map(e -> fields.stream().map(f -> adapt2Mysql(e, f)).collect(Collectors.joining(",", "(", ")")))
                        .collect(Collectors.joining(",", "INSERT INTO " + table + fields.stream().collect(Collectors.joining(",", " (", ") ")) + "VALUES ", ";")))
                .toList();

        AtomicInteger counter = new AtomicInteger(0);

        // insert
        try {
            DbFactory.get(database).tx(db -> {
                for (String sql : insertSqls) {
                    counter.addAndGet(db.execute(sql));
                }
            });
        } catch (SQLException e) {
            throw new Exc(e, "postgresql batch insert failed");
        }

        return counter.get();
    }

    /**
     * @param  database     db
     * @param  table
     * @param  indexedField 索引字段 用来判断数据的唯一性
     * @param  filteredBy   筛选字段
     * @param  dataList     new data
     * @author              Vec 2023年11月17日 14:40:57
     */
    public static Integer distinctAndBatchInsert(String database, String table, String indexedField, String sortedTimeField, LocalDateTime afterTime, List<JSONObject> dataList) {
        if (dataList.isEmpty()) {
            return 0;
        }

        List<String> existedIndexes = new ArrayList<>();
        try {
            existedIndexes
                    .addAll(DbFactory.get(database).query(StrUtil.format("""
                                                                         SELECT
                                                                             {}
                                                                         FROM
                                                                             {}
                                                                         WHERE
                                                                             {} >= '{}'
                                                                         """, indexedField, table, sortedTimeField, Const.DATE_TIME_FORMATTER.format(afterTime)), String.class));
        } catch (SQLException e) {
            StaticLog.error(e);
            throw new RuntimeException("mysql queries failed", e);
        }

        return batchInsert(database, table, dataList.stream().filter(v -> !existedIndexes.contains(v.getStr(indexedField))).toList());
    }

    /**
     * 将数据分段
     * @param  sources  数据源
     * @param  eachSize 每段大小
     * @return          分段后的新集合
     * @author          52291590 2022年11月17日 上午10:25:07
     */
    private static List<List<JSONObject>> segment(List<JSONObject> sources, int eachSize) {
        return Stream.iterate(0, i -> i < sources.size(), i -> i + eachSize)
                .map(i -> sources.size() - i >= eachSize ? sources.subList(i, i + eachSize) : sources.subList(i, sources.size()))
                .toList();
    }

    /**
     * 适配字段值 以符合mysql要求
     * @author 52291590 2022年11月18日 上午9:15:41
     */
    private static String adapt2Mysql(JSONObject e, String field) {
        Object o = e.get(field);
        if (o == null) {
            return "''";
        }

        // 特殊处理下时间
        return StrUtil.format("'{}'", field.toLowerCase().contains("time") ? formatTime(e.getStr(field)) : e.getStr(field));
    }

    /**
     * 解决毫秒问题 e.g "2022-11-22 16:45:40.0"
     * @author 52291590 2022年11月22日 下午5:04:38
     */
    private static String formatTime(String timeStr) {
        int i = timeStr.indexOf(".");
        return i == -1 ? timeStr : timeStr.substring(0, i);
    }

}
